#!/usr/bin/php -q
<?php
/**
 * 利用反向工程从数据库information_schema中生成数据字典
 * 输入格式Markdown，可以直接推到Git服务器。
 * 要求每个表每个字段都有说明信息
 * 使用方法：ddre.php dbname > dbname.md
 */
$filename = "databases";
$handle = fopen($filename, "r");
$contents = fread($handle, filesize($filename));
fclose($handle);
$dbs = json_decode($contents);
foreach ($dbs as $db) {
	echo 'Server: ', $db->host, PHP_EOL, 'Port:', $db->port, PHP_EOL, 'Database:', $db->name, PHP_EOL;
	if (property_exists($db, 'alias')) {
		writefile($db->host, $db->port, $db->name, $db->alias);
	}
	else {
		writefile($db->host, $db->port, $db->name, $db->name);
	}
	if (!($db === end($dbs))) {
		echo '----------------------------------------', PHP_EOL;
	}
}
die();

function writefile($host, $port, $dbname, $alias)
{
	$connection = new PDO("mysql:host={$host};dbname=information_schema;port={$port}", 'root', 'changme');
	$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$connection->exec('set names utf8');
	
	$sql_tables = "SELECT TABLE_NAME,TABLE_COMMENT,TABLE_COLLATION,ENGINE FROM `TABLES` WHERE `TABLE_SCHEMA` = :schema";
	$stmt_tables = $connection->prepare($sql_tables);
	$stmt_tables->execute([':schema'=>$dbname]);
	
	$tables = $stmt_tables->fetchAll(PDO::FETCH_ASSOC);
	
	/**
	 * Regular Expression to Replace INT(#) TO INT
	 */
	$pattern = '/((?:INT|BIGINT|TINYINT|SMALLINT|MEDIUMINT))\s*\(\d+\)/';
	/**
	 * $test = 'BIGINT(11) UNSIGNED';
	 * echo preg_replace($pattern, '${1}', $test);
	 * exit;
	 */
	$search = ["\r\n", "\n", "\r"];
	$replacement = '';
	$handle = fopen("{$alias}.md", "w+");
	if(!$handle) die("Can't open file {$alias}.sql for write.\n");

	foreach ($tables as $table) {
		$table_name = $table['TABLE_NAME'];
		$table_comment = $table['TABLE_COMMENT'];
		$table_comment = ltrim(rtrim(str_replace($search, $replacement, $table_comment)));
		$table_collation = $table['TABLE_COLLATION'];
		$table_engine = $table['ENGINE'];
		$table_charset = explode('_', $table_collation)[0];
	
		fwrite($handle, "#### 表名：{$table_name}" . PHP_EOL);
		fwrite($handle, "* 用途描述：{$table_comment}" . PHP_EOL);
		fwrite($handle, "* 存储引擎：{$table_engine}" . PHP_EOL);
		fwrite($handle, "* 字符集：　{$table_charset}" . PHP_EOL);
		fwrite($handle, "* 排序规则：{$table_collation}\n" . PHP_EOL);
		fwrite($handle, "| 列名      | 允许空    | 默认值    | 键  | 类型      | 说明     |" . PHP_EOL);
		fwrite($handle, "|:----------|:----------|:----------|:----|:----------|:---------|" . PHP_EOL);
	
		$sql_columns = "SELECT COLUMN_NAME,IS_NULLABLE,COLUMN_DEFAULT,COLUMN_KEY, COLUMN_TYPE,COLUMN_COMMENT FROM COLUMNS WHERE TABLE_SCHEMA = :schema AND TABLE_NAME = :table ORDER BY ORDINAL_POSITION;";
		$stmt_columns = $connection->prepare($sql_columns);
		/**
		 * $stmt_columns->bindParam(':schema', $dbname, PDO::PARAM_STR);
		 * $stmt_columns->bindParam(':table', $table_name, PDO::PARAM_STR);
		 */
		$stmt_columns->execute([':schema'=>$dbname, ':table' => $table_name]);
		$nullables = ['YES' => '是', 'NO' => '否'];
		$columns = $stmt_columns->fetchAll(PDO::FETCH_ASSOC);
		foreach ($columns as $column) {
			$name = $column['COLUMN_NAME'];
			$nullable = $nullables[$column['IS_NULLABLE']];
			$key = $column['COLUMN_KEY'] == 'PRI' ? '主键' : '';
			$comment = $column['COLUMN_COMMENT'];
			$comment = ltrim(rtrim(str_replace($search, $replacement, $comment)));
			$type = strtoupper($column['COLUMN_TYPE']);
			$type = preg_replace($pattern, '${1}', $type);
			$default = $column['COLUMN_DEFAULT'] ?? '无';
			$default = $default == '' ? '\'\'' : $default;
			fwrite($handle, sprintf("| %s| %s| %s| %s| %s| %s|", $name, $nullable, $default, $key, $type, $comment) . PHP_EOL);
		}
		if ($table === end($tables)) {
		}
		else {
			fwrite($handle, "***" . PHP_EOL);
		}
	}
	fclose($handle);
}
